import java.sql.*;
public class DatabaseControl {
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
    static final String DB_URL = "jdbc:mysql://localhost:3306/hourseworkLibrary?characterEncoding=UTF-8";
    // 数据库的用户名与密码，需要根据自己的设置
    static final String USER = "root";
    static final String PASS = "12345678";
    
    private Connection conn=null;
     private Statement stmt = null;
     
     public DatabaseControl() {
    	 this.toConnecting();
     }
    private void toConnecting() {
		// TODO Auto-generated method stub
       
        
            // 注册 JDBC 驱动
            try {
				Class.forName(JDBC_DRIVER);
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        
            try {
				conn = DriverManager.getConnection(DB_URL,USER,PASS);
				System.out.println("连接数据库成功！！！");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    }
    
    public Connection getConn() {
    	return this.conn;
    }
    public void initTable() {
    	try {
			stmt = conn.createStatement();
			stmt.executeUpdate("create table test(id int, name varchar(80))");
			System.out.println("成功创建表"); 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("数据库中已存在"); 
			e.printStackTrace();
		}
    	
    }
    private void addManager(String name, String pwd) {
    	String sql = "insert into manager values (?,?)";
    	

		try {
			PreparedStatement stmt = (PreparedStatement) conn.prepareStatement(sql);
			stmt.setString(1, name);
			stmt.setString(2, pwd);
			stmt.executeUpdate();
			System.out.println("创建管理员成功");
		}catch (SQLException e) {
			// TODO 自动生成的 catch 块
			System.out.println("该管理员已存在");
			e.printStackTrace();
		}    	
    }
    public void addNewUser(String name, String pwd) {
    	String sql = "insert into user values (?,?)";
    	

		try {
			PreparedStatement stmt = (PreparedStatement) conn.prepareStatement(sql);
			stmt.setString(1, name);
			stmt.setString(2, pwd);
			stmt.executeUpdate();
			System.out.println("创建用户成功");
		}catch (SQLException e) {
			// TODO 自动生成的 catch 块
			System.out.println("该用户已存在");
			e.printStackTrace();
		}
	}

    public boolean checkManager(String name, String pwd) {
    	boolean flag=false;
    	try {
    	 stmt = conn.createStatement();//实例化Statement对象，用stmt接收
         String sql;//定义一个string类型的数据变量sql
         sql = "SELECT manager_name, password FROM manager";//用sql接收数据库语句
         ResultSet rs = stmt.executeQuery(sql); //执行sql
         
         // 展开结果集数据库
         while(rs.next()){
             // 通过字段检索
             String manager_name = rs.getString("manager_name");
             String password = rs.getString("password");
             if(manager_name.equals(name)) {
            	 if(password.equals(pwd)) {
            		 flag=true;
            		 break;
            	 }else {
            		 System.out.println("密码有误");
            		 flag=false;
            		 return flag;
            	 }
             }
         }
    	}catch(Exception e) {}
    	if(!flag) {
    		 System.out.println("管理员不存在");
    		return flag;
    	}else {
    		return flag;
    	}
    }
    public boolean checkUserNameIsExist(String name) {
    	boolean flag=false;
    	try {
       	 stmt = conn.createStatement();//实例化Statement对象，用stmt接收
            String sql;//定义一个string类型的数据变量sql
            sql = "SELECT user_name, password FROM user";//用sql接收数据库语句
            ResultSet rs = stmt.executeQuery(sql); //执行sql
            
            // 展开结果集数据库
            while(rs.next()){
                // 通过字段检索
                String user_name = rs.getString("user_name");
                if(user_name.equals(name)) {
                	flag=true;
                	break;
                }
            }
       	}catch(Exception e) {}
    	return flag;
    }
    
    public boolean checkUser(String name, String pwd) {
    	boolean flag=false;
    	try {
    	 stmt = conn.createStatement();//实例化Statement对象，用stmt接收
         String sql;//定义一个string类型的数据变量sql
         sql = "SELECT user_name, password FROM user";//用sql接收数据库语句
         ResultSet rs = stmt.executeQuery(sql); //执行sql
         
         // 展开结果集数据库
         while(rs.next()){
             // 通过字段检索
             String user_name = rs.getString("user_name");
             String password = rs.getString("password");
             if(user_name.equals(name)) {
            	 if(password.equals(pwd)) {
            		 flag=true;
            		 break;
            	 }else {
            		 System.out.println("密码有误");
            		 flag=false;
            		 return flag;
            	 }
             }
         }
    	}catch(Exception e) {}
    	if(!flag) {
    		 System.out.println("该用户不存在");
    		return flag;
    	}else {
    		return flag;
    	}
    }
	
    public boolean checkBookNoIsExist(String no) {
    	boolean flag=false;
    	try {
    	 stmt = conn.createStatement();//实例化Statement对象，用stmt接收
         String sql;//定义一个string类型的数据变量sql
         sql = "SELECT book_no FROM book";//用sql接收数据库语句
         ResultSet rs = stmt.executeQuery(sql); //执行sql
         
         // 展开结果集数据库
         while(rs.next()){
             // 通过字段检索
             String book_no = rs.getString("book_no");
             if(book_no.equals(no)) {
            	 flag=true;
            	 break;
             }
         }
    	}catch(Exception e) {}
    	return flag;
    }
    
    public void addNewBook(String book_no,String book_name,String classify,String author,int store_count,String summary,String area,String publish_time,String community) {
		String sql = "insert into book values (?,?,?,?,?,?,?,?,?)";
		try {
			PreparedStatement stmt = (PreparedStatement) conn.prepareStatement(sql);

			stmt.setString(1, book_no);
			stmt.setString(2, book_name);
			stmt.setString(3, classify);
			stmt.setString(4, author);
			stmt.setInt(5, store_count);
			stmt.setString(6, summary);
			stmt.setString(7, area);
			stmt.setString(8, publish_time);
			stmt.setString(9, community);
			stmt.executeUpdate();
			System.out.println("添加新书成功！！");
			System.out.println();
		}catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} 
    }
    public static void main(String[] args) throws SQLException {
//		DatabaseControl sql=new DatabaseControl();
//		sql.addManager("caster", "123qwe");

	}


}
